package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.TrialForm;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.TryJob;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 试用表 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2025-06-10
 */
public interface TrialFormMapper extends BaseMapper<TrialForm> {




    @Select({"<script>",
            "SELECT tf.*,r.id as recruit_id " ,
            "FROM",
            "adms_trial_form AS tf " ,
            "LEFT JOIN adms_try_job tj ON tf.try_job_id = tj.id" ,
            "LEFT JOIN adms_recruit r ON r.id = tj.recruit_id " ,
            "WHERE " ,
            "1=1 and tf.type = 1" ,
            "<if test= 'type!=null and type!=&apos;&apos; '>",

            "<when test='type == 0 '>",
            "AND tf.pass_type = ${type} AND tf.is_give_up = 0",
            "</when>",
            "<when test='type == 1 or type == 2'>",
            "AND tf.pass_type = ${type} AND tf.is_give_up = 0",
            "</when>",
            "<when test='type == 3'>",
            "AND tf.pass_type = 0 AND tf.is_give_up = 1",
            "</when>",

            "</if>" ,
//            "<if test='schoolId!=null and schoolId!=&apos;&apos; '>",
//            "AND tj.school_id IN ( ${schoolId} )",
//            "</if>",

            "<if test='name!=null and name!=&apos;&apos; '>",
            "AND r.name LIKE CONCAT('%', #{name}, '%')",
            "</if>",
            //在职
            "<if test='type ==4 '>",
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND tf.is_pass_time BETWEEN '${startDate} 00:00:00' AND '${endDate} 23:59:59' AND tf.pass_type = 1 and tf.is_resign = 0",
            "</if>",
            "ORDER BY tf.is_pass_time DESC ",
            "</if>",
            //离职
            "<if test='type ==5'>",
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND tf.is_resign_time BETWEEN '${startDate} 00:00:00' AND '${endDate} 23:59:59' AND tf.pass_type = 1 and tf.is_resign = 1",
            "</if>",
            "ORDER BY tf.is_pass_time DESC ",
            "</if>",

            "<if test='type ==0 or type ==1 or type ==2 or type ==3 '>",
            "<if test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND tf.trial_date &gt;= #{startDate} AND tf.trial_date &lt;= #{endDate}",
            "</if>",
            "ORDER BY tf.trial_date DESC ",
            "</if>",
            "</script>"})
    IPage<TrialForm> queryTrialList(IPage<TrialForm> page, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("type") String type, @Param("name") String name, @Param("schoolId") String schoolId);


    @Select({"<script>",
            "WITH teacher_with AS (",
            "    SELECT id, name, IF(role_admin.admin_id IS NULL, 1, 2) AS is_person",
            "    FROM staff",
            "    LEFT JOIN role_admin ON staff.id = role_admin.admin_id AND role_admin.role_id = 1568160915052400644",
            ")",
            "SELECT",
            "    COUNT(CASE WHEN tf.trial_date BETWEEN #{startDate} AND #{endDate} THEN 1 END) AS trialCount,",
            "    COUNT(CASE WHEN tf.give_up_time BETWEEN #{startDate} AND #{endDate} AND tf.is_give_up = 1 THEN 1 END) AS trialGiveUpCount,",
            "    COUNT(CASE WHEN tf.is_pass_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 1 THEN 1 END) AS trialPassCount,",
            "    COUNT(CASE WHEN tf.is_pass_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 2 THEN 1 END) AS trialNotPassCount,",
            "    COUNT( CASE WHEN tf.is_pass_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 1 AND tf.is_resign = 0 THEN 1 END ) AS workCount,",
            "    COUNT( CASE WHEN tf.is_resign_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 1 AND tf.is_resign = 1 THEN 1 END ) AS notWorkCount,",
            "    (CASE WHEN t2.type = 1 THEN getSchoolName(t1.school_id) ELSE t3.name END) AS school_name,",
            "    (CASE WHEN t2.type = 1 THEN t1.school_id ELSE t3.id END) AS school_cross_id",
            "FROM adms_trial_form tf",
            "LEFT JOIN adms_try_job AS t1 ON tf.try_job_id = t1.id",
            "LEFT JOIN adms_recruit r ON r.id = t1.recruit_id",
            "INNER JOIN adms_station_needs AS t2 ON t1.station_needs_id = t2.id",
            "LEFT JOIN department AS t3 ON t2.department_id = t3.id",
            "INNER JOIN teacher_with AS t4 ON r.inviter_id = t4.id",
            "WHERE 1=1",
            "  AND tf.type = 1",
            "<if test='departmentId != null and departmentId != &apos;&apos;'>",
            "  AND t2.department_id = ${departmentId}",
            "</if>",
            "<if test='stationsId != null and stationsId != &apos;&apos;'>",
            "  AND t2.station_id IN ( ${stationsId} )",
            "</if>",
            "<if test='labourId != null and labourId != &apos;&apos;'>",
            "  AND t2.labour_id = ${labourId}",
            "</if>",
            "<if test='type != null and type != &apos;&apos;'>",
            "  AND t4.is_person = ${type}",
            "</if>",
            "<if test='theirType != null and theirType != &apos;&apos;'>",
            "  AND t2.type = ${theirType}",
            "</if>",
            "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
            "  AND t2.subjects_id IN (${subjectsId})",
            "</if>",
            "GROUP BY school_cross_id",
            "</script>"})
    public List<TrialForm> trialPassRate(@Param("startDate") String startDate,
                                          @Param("endDate") String endDate,
                                          @Param("departmentId") String departmentId,
                                          @Param("stationsId") String stationsId,
                                          @Param("labourId") String labourId,
                                          @Param("type") String type,
                                          @Param("yearPart") String yearPart,
                                          @Param("quarterNum") String quarterNum,
                                          @Param("dateType") String dateType,
                                          @Param("theirType") String theirType,
                                          @Param("subjectsId") String subjectsId);


    @Select({"<script>",
            "WITH teacher_with AS (",
            "    SELECT id, name, IF(role_admin.admin_id IS NULL, 1, 2) AS is_person",
            "    FROM staff",
            "    LEFT JOIN role_admin ON staff.id = role_admin.admin_id AND role_admin.role_id = 1568160915052400644",
            ")",
            "SELECT",
            "    t2.NAME AS stationName,",
            "    r.NAME",
//            "    COUNT(CASE WHEN tf.is_pass_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 1 THEN 1 END) AS trialPassCount,",
//            "    COUNT(CASE WHEN tf.is_pass_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 2 THEN 1 END) AS trialNotPassCount,",
//            "    (CASE WHEN t2.type = 1 THEN getSchoolName(t1.school_id) ELSE t3.name END) AS school_name,",
//            "    (CASE WHEN t2.type = 1 THEN t1.school_id ELSE t3.id END) AS school_cross_id",
            "FROM adms_trial_form tf",
            "LEFT JOIN adms_try_job AS t1 ON tf.try_job_id = t1.id",
            "LEFT JOIN adms_recruit r ON r.id = t1.recruit_id",
            "INNER JOIN adms_station_needs AS t2 ON t1.station_needs_id = t2.id",
            "LEFT JOIN department AS t3 ON t2.department_id = t3.id",
            "INNER JOIN teacher_with AS t4 ON r.inviter_id = t4.id",
            "WHERE 1=1",
            "  AND tf.type = 1",
            "<if test='departmentId != null and departmentId != &apos;&apos;'>",
            "  AND t2.department_id = ${departmentId}",
            "</if>",
            "<if test='stationsId != null and stationsId != &apos;&apos;'>",
            "  AND t2.station_id IN ( ${stationsId} )",
            "</if>",
            "<if test='labourId != null and labourId != &apos;&apos;'>",
            "  AND t2.labour_id = ${labourId}",
            "</if>",
            "<if test='type != null and type != &apos;&apos;'>",
            "  AND t4.is_person = ${type}",
            "</if>",
            "<if test='theirType != null and theirType != &apos;&apos;'>",
            "  AND t2.type = ${theirType}",
            "</if>",
            "<if test='subjectsId != null and subjectsId != &apos;&apos;'>",
            "  AND t2.subjects_id IN (${subjectsId})",
            "</if>",
            "<if test='recruitStatus != null and recruitStatus != &apos;&apos;'>",
            "<if test='recruitStatus == 0'>",
            "AND tf.trial_date BETWEEN #{startDate} AND #{endDate}",
            "</if>",

            "<if test='recruitStatus == 1'>",
            "AND tf.give_up_time BETWEEN #{startDate} AND #{endDate} AND tf.is_give_up = 1",
            "</if>",
            "<if test='recruitStatus == 2'>",
            "AND tf.is_pass_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 1",
            "</if>",
            "<if test='recruitStatus == 3'>",
            "AND tf.is_pass_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 2",
            "</if>",

            //在职总人数
            "<if test='recruitStatus == 4'>",
            "AND tf.is_pass_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 1 AND tf.is_resign = 0",
            "</if>",

            //离职总人数
            "<if test='recruitStatus == 5'>",
            "AND tf.is_resign_time BETWEEN #{startDate} AND #{endDate} AND tf.pass_type = 1 AND tf.is_resign = 1",
            "</if>",

            "  AND (t2.department_id =${schoolCrossId}  OR t1.school_id = ${schoolCrossId})",
            "</if>",

            "</script>"})
    public IPage<TrialForm> trialPassRateDetails(IPage<?> page,
                                         @Param("startDate") String startDate,
                                         @Param("endDate") String endDate,
                                         @Param("departmentId") String departmentId,
                                         @Param("stationsId") String stationsId,
                                         @Param("labourId") String labourId,
                                         @Param("type") String type,
                                         @Param("schoolCrossId") String schoolCrossId,
                                         @Param("yearPart") String yearPart,
                                         @Param("quarterNum") String quarterNum,
                                         @Param("dateType") String dateType,
                                         @Param("recruitStatus") String recruitStatus,
                                         @Param("theirType") String theirType,
                                         @Param("subjectsId") String subjectsId);



    @Select({
            "SELECT",
            "    MIN(trial_date) AS minTrialDate,",
            "    MAX(trial_date) AS maxTrialDate",
            "FROM",
            "    adms_trial_form",
            "where type = 1",
    })
    Map<String, Object> trialFormDate();

    @Select({
            "SELECT",
            "MIN( Date(is_pass_time) ) AS minTrialDate,",
            "MAX( CASE WHEN DATE(is_resign_time) IS NOT NULL THEN DATE(is_resign_time) ELSE DATE(is_pass_time) END ) AS  maxTrialDate",
            "FROM adms_trial_form WHERE type = 1"
    })
    Map<String, Object> trialChangeDate();
}
